﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.Drawing.Imaging
Public Class Frm_Gestion_Clients
    Public str As String = ""
    Public num As Integer = 0
    Public modif As Integer = 0

    Public Sub com_ville()
        Try
            comb_ville.Text = ""
            comb_ville.Items.Clear()
            Cmd = New SqlCommand("Select * From Comb_Ville", Con)
            open()
            Dr = Cmd.ExecuteReader()
            Do While Dr.Read()
                comb_ville.Items.Add(Dr("Ville"))
            Loop
            Dr.Close()
            cnClose()
            If comb_ville.Items.Count > 0 Then
                comb_ville.SelectedIndex = 0
            End If
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
    End Sub


    Public Sub Afficher()
        Try
            List_tous.Items.Clear()
            Cmd = New SqlCommand("Select * From Clients", Con)
            open()
            Dr = Cmd.ExecuteReader()
            Do While Dr.Read()
                Dim Line As ListViewItem = New ListViewItem(New String() {Dr("Nom"), Dr("Prenom"), Dr("Adresse"), Dr("Nom_entreprise"), Dr("Nom_interlocuteur"), Dr("Cin"), Dr("Ville"), Dr("Type_Client"), Dr("Id_Client")})
                List_tous.Items.Add(Line)
            Loop
            Dr.Close()
            cnClose()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
    End Sub

    Public Sub vider()
        txt_nom.Clear()
        txt_prenom.Clear()
        txt_cin.Clear()
        txt_adresse.Clear()
        txt_mail.Clear()
        txt_nom_entrep.Clear()
        txt_nom_interl.Clear()
        txt_portabl.Clear()
        txt_fix.Clear()
        txt_fax.Clear()
        txt_remarq.Clear()
        Radio_partc.Checked = True
        Radio_entr.Checked = False
        comb_ville.SelectedIndex = 0
        Pic_photo.Image = My.Resources.photo
        txt_nom.Select()
        num = 0
        modif = 0
        Check_loc.Checked = False
        List_tous.SelectedItems.Clear()
        With List_tous
            If .SelectedIndices.Count > 0 Then
                For i As Integer = 0 To .Items.Count - 1
                    .Items(i).Selected = False
                    .Items(i).Focused = False
                Next
            End If
        End With
    End Sub

    Private Sub btn_parcour_ville_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_parcour_ville.Click
        Me.Enabled = False
        Frm_ville.txt_ville.Select()
        Frm_ville.Show()
    End Sub

    Private Sub Radio_partc_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Radio_partc.CheckedChanged
        If Radio_partc.Checked = True Then
            txt_nom_entrep.Enabled = False
            txt_nom_interl.Enabled = False

            txt_prenom.Enabled = True
            txt_nom.Enabled = True
            txt_cin.Enabled = True

            txt_nom_entrep.Clear()
            txt_nom_interl.Clear()

            txt_nom.Select()
            str = "Particulier"
        End If
    End Sub

    Private Sub Radio_entr_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Radio_entr.CheckedChanged
        If Radio_entr.Checked = True Then
            txt_prenom.Enabled = False
            txt_nom.Enabled = False
            txt_cin.Enabled = False

            txt_nom_entrep.Enabled = True
            txt_nom_interl.Enabled = True

            txt_prenom.Clear()
            txt_nom.Clear()
            txt_cin.Clear()

            txt_nom_entrep.Select()
            str = "Entreprise"
        End If
    End Sub

    Private Sub Frm_Gestion_Clients_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Afficher()
        com_ville()
        txt_nom.Select()
    End Sub

    Private Sub btn_nouv_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_nouv.Click
        vider()
    End Sub

    Private Sub btn_supp_phot_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_supp_phot.Click
        Pic_photo.Image = My.Resources.photo

    End Sub

    Private Sub btn_parcour_photo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_parcour_photo.Click
        With OpenF
            .FileName = ""
            .Title = "Ovrir la Photo"
            .RestoreDirectory = True
            .InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
            .Filter = "JPEG Files (*.JPG)|*.JPG"
            .FilterIndex = 1
        End With
        If OpenF.ShowDialog = Windows.Forms.DialogResult.OK Then
            Pic_photo.Image = Image.FromFile(OpenF.FileName)

        End If
    End Sub

    Private Sub btn_ajouter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_ajouter.Click

        If (txt_nom.Text <> "" And txt_prenom.Text <> "") Or (txt_nom_entrep.Text <> "" And txt_nom_interl.Text <> "") Then
            Dim var As Boolean = regEmail(txt_mail.Text)

            If txt_mail.Text = "" Then
                var = True
            End If
            If var = True Then
                If MsgBox("Vous Voulez Vraiment Ajouter Ce Client ?", MsgBoxStyle.YesNo Or MsgBoxStyle.Information, "Ajouter Client") = MsgBoxResult.Yes Then
                    Try
                        'Tarik(Pour l'héritage voir location diagrame)
                        Cmd = New SqlCommand("INSERT INTO [Salle_Mariage].[dbo].[locEvent]" &
                                            " select MAX(id) + 1 from locEvent ", Con)
                        open()
                        Cmd.ExecuteNonQuery()

                        Cmd.CommandText = "select MAX(id) from locEvent"
                        Dim idClt As Integer = Cmd.ExecuteScalar

                        Cmd.CommandText = "insert into Clients values (" & idClt & ",@nom,@pren,@cin,@adres,@email,@Location,@entre,@interl,@vill,@type,@remarq,@img,@Portable,@Fax,@Fix)"

                        Cmd.Parameters.Add("@nom", SqlDbType.VarChar).Value = txt_nom.Text
                        Cmd.Parameters.Add("@pren", SqlDbType.VarChar).Value = txt_prenom.Text
                        Cmd.Parameters.Add("@cin", SqlDbType.VarChar).Value = txt_cin.Text
                        Cmd.Parameters.Add("@adres", SqlDbType.VarChar).Value = txt_adresse.Text
                        Cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = txt_mail.Text
                        If Check_loc.Checked = True Then
                            Cmd.Parameters.Add("@Location", SqlDbType.VarChar).Value = "Oui"
                        ElseIf Check_loc.Checked = False Then
                            Cmd.Parameters.Add("@Location", SqlDbType.VarChar).Value = "Non"
                        End If
                        Cmd.Parameters.Add("@entre", SqlDbType.VarChar).Value = txt_nom_entrep.Text
                        Cmd.Parameters.Add("@interl", SqlDbType.VarChar).Value = txt_nom_interl.Text
                        Cmd.Parameters.Add("@vill", SqlDbType.VarChar).Value = comb_ville.SelectedItem
                        Cmd.Parameters.Add("@type", SqlDbType.VarChar).Value = str
                        Cmd.Parameters.Add("@remarq", SqlDbType.Text).Value = txt_remarq.Text
                        Cmd.Parameters.Add("@Portable", SqlDbType.Text).Value = txt_portabl.Text
                        Cmd.Parameters.Add("@Fax", SqlDbType.Text).Value = txt_fax.Text
                        Cmd.Parameters.Add("@Fix", SqlDbType.Text).Value = txt_fix.Text

                        'اضافة الصورة


                        Dim ms As MemoryStream = New MemoryStream()
                        Pic_photo.Image.Save(ms, ImageFormat.Jpeg)
                        Dim bytBLOBData(ms.Length - 1) As Byte
                        ms.Position = 0
                        ms.Read(bytBLOBData, 0, ms.Length)
                        Dim prm As New SqlParameter("@img", SqlDbType.VarBinary, _
                        bytBLOBData.Length, ParameterDirection.Input, False, _
                        0, 0, Nothing, DataRowVersion.Current, bytBLOBData)
                        Cmd.Parameters.Add(prm)


                        Cmd.ExecuteNonQuery()
                        cnClose()

                        MsgBox("Client Ajouté", MsgBoxStyle.Information, "Réussi !")
                        vider()
                        Afficher()

                    Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                        cnClose()
                    End Try
                End If
            Else
                MsgBox("Entrez l'email Correct", MsgBoxStyle.Critical, "Erreur !")
            End If
        Else
            MsgBox("Entrez les Données", MsgBoxStyle.Critical, "Erreur !")
        End If




    End Sub

    Private Sub List_tous_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles List_tous.SelectedIndexChanged
        Dim n As Integer
        With List_tous
            If .SelectedIndices.Count > 0 Then
                For i As Integer = 0 To .SelectedIndices.Count - 1
                    n = CInt(.Items(.SelectedIndices(i)).SubItems(8).Text)
                    modif = CInt(.Items(.SelectedIndices(i)).SubItems(8).Text)
                Next
            End If
        End With

        Try
            Cmd = New SqlCommand("Select * From Clients where Id_Client =" & n, Con)
            open()
            Dr = Cmd.ExecuteReader()
            Dr.Read()
            txt_nom.Text = Dr("Nom")
            txt_prenom.Text = Dr("Prenom")
            txt_cin.Text = Dr("Cin")
            txt_adresse.Text = Dr("Adresse")
            txt_mail.Text = Dr("Email")
            If Dr("Location") = "Oui" Then
                Check_loc.Checked = True
            Else
                Check_loc.Checked = False
            End If
            txt_nom_entrep.Text = Dr("Nom_entreprise")
            txt_nom_interl.Text = Dr("Nom_interlocuteur")
            txt_remarq.Text = Dr("Remarque")
            comb_ville.Text = Dr("Ville")
            If Dr("Type_Client") = "Particulier" Then
                Radio_partc.Checked = True
            ElseIf Dr("Type_Client") = "Entreprise" Then
                Radio_entr.Checked = True
            ElseIf Dr("Type_Client") = "Traiteur" Then
                Radio_trait.Checked = True
            End If
            txt_portabl.Text = Dr("Portable")
            txt_fix.Text = Dr("Fix")
            txt_fax.Text = Dr("Fax")
            Dr.Close()
            cnClose()
        Catch ex As Exception
            'MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try

        'جلب الصورة
        Cmd = New SqlCommand("SELECT Photo FROM Clients Where Id_Client =" & n, Con)
        Da = New SqlDataAdapter(Cmd)
        Ds = New DataSet()
        Da.Fill(Ds, "Clients")
        Dim c As Integer = Ds.Tables("Clients").Rows.Count
        If c > 0 Then
            Dim bytBLOBData() As Byte = _
                Ds.Tables("Clients").Rows(c - 1)("Photo")
            Dim stmBLOBData As New MemoryStream(bytBLOBData)
            Pic_photo.Image = Image.FromStream(stmBLOBData)
        End If
    End Sub
    Private Sub btn_recher_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_recher.Click
        Me.Enabled = False
        Frm_Recherche_clients.Show()
        rech = "client"
    End Sub

    Private Sub btn_supp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_supp.Click
        Dim n As Integer
        With List_tous
            If .SelectedIndices.Count > 0 Then
                For i As Integer = 0 To .SelectedIndices.Count - 1
                    n = CInt(.Items(.SelectedIndices(i)).SubItems(8).Text)
                Next
                If MsgBox("Voulez Vous Vraiment Supprimer ce Client", MsgBoxStyle.YesNo Or MsgBoxStyle.Information, "Suppréssion !") = MsgBoxResult.Yes Then

                    Try
                        'pour supprimer tout les demandes de la table locEvent
                        Cmd = New SqlCommand("select id_dem from Clients_Demande where Id_Client = " & n, Con)
                        open()
                        Dr = Cmd.ExecuteReader
                        Dim str As String = "("
                        While Dr.Read
                            str &= Dr.GetInt32(0) & ","
                        End While
                        Dr.Close()
                        If str <> "(" Then
                            str = str.Remove(str.Length - 1, 1)
                            str = str.Insert(str.Length, ")")
                            Cmd.CommandText = "delete from locEvent where id in " & str
                            Cmd.ExecuteNonQuery()
                        End If

                        Cmd.CommandText = "Delete From Clients Where Id_client =" & n
                        Cmd.ExecuteNonQuery()
                        'tarik
                        Cmd.CommandText = "delete from locEvent where id = " & n
                        Cmd.ExecuteNonQuery()


                        cnClose()
                        Afficher()
                        vider()
                        MsgBox("Suppresion effectué !", MsgBoxStyle.Information, "Terminer")
                    Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                        cnClose()
                    End Try


                End If
            Else
                MsgBox("Vous n'avez selectionné aucun Client !", MsgBoxStyle.Critical, "Réssayer !")
            End If
        End With

    End Sub

    Private Sub btn_modif_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_modif.Click

        If (txt_nom.Text <> "" And txt_prenom.Text <> "") Or (txt_nom_entrep.Text <> "" And txt_nom_interl.Text <> "") And modif <> 0 Then
            Dim var As Boolean = regEmail(txt_mail.Text)

            If txt_mail.Text = "" Then
                var = True
            End If

            If var = True Then
                If MsgBox("Vous Voulez Vraiment Modifier Ce Client ?", MsgBoxStyle.YesNo Or MsgBoxStyle.Information, "Modifier Client") = MsgBoxResult.Yes Then
                    Try
                        Cmd = New SqlCommand("update Clients set Nom=@nom,Prenom=@pren,Cin=@cin,Adresse=@adres,Email=@email,Location=@Location,Nom_entreprise=@entre,Nom_interlocuteur=@interl,Ville=@vill,Type_Client=@type,Remarque=@remarq,Photo=@img,Portable=@Portable,Fax=@Fax,Fix=@Fix" & _
                        " Where Id_client=" & modif, Con)

                        Cmd.Parameters.Add("@nom", SqlDbType.VarChar).Value = txt_nom.Text
                        Cmd.Parameters.Add("@pren", SqlDbType.VarChar).Value = txt_prenom.Text
                        Cmd.Parameters.Add("@cin", SqlDbType.VarChar).Value = txt_cin.Text
                        Cmd.Parameters.Add("@adres", SqlDbType.VarChar).Value = txt_adresse.Text
                        Cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = txt_mail.Text
                        If Check_loc.Checked = True Then
                            Cmd.Parameters.Add("@Location", SqlDbType.VarChar).Value = "Oui"
                        ElseIf Check_loc.Checked = False Then
                            Cmd.Parameters.Add("@Location", SqlDbType.VarChar).Value = "Non"
                        End If
                        Cmd.Parameters.Add("@entre", SqlDbType.VarChar).Value = txt_nom_entrep.Text
                        Cmd.Parameters.Add("@interl", SqlDbType.VarChar).Value = txt_nom_interl.Text
                        Cmd.Parameters.Add("@vill", SqlDbType.VarChar).Value = comb_ville.SelectedItem
                        Cmd.Parameters.Add("@type", SqlDbType.VarChar).Value = str
                        Cmd.Parameters.Add("@remarq", SqlDbType.Text).Value = txt_remarq.Text
                        Cmd.Parameters.Add("@Portable", SqlDbType.Text).Value = txt_portabl.Text
                        Cmd.Parameters.Add("@Fax", SqlDbType.Text).Value = txt_fax.Text
                        Cmd.Parameters.Add("@Fix", SqlDbType.Text).Value = txt_fix.Text
                        'اضافة الصورة


                        Dim ms As MemoryStream = New MemoryStream()
                        Pic_photo.Image.Save(ms, ImageFormat.Jpeg)
                        Dim bytBLOBData(ms.Length - 1) As Byte
                        ms.Position = 0
                        ms.Read(bytBLOBData, 0, ms.Length)
                        Dim prm As New SqlParameter("@img", SqlDbType.VarBinary, _
                        bytBLOBData.Length, ParameterDirection.Input, False, _
                        0, 0, Nothing, DataRowVersion.Current, bytBLOBData)
                        Cmd.Parameters.Add(prm)


                        open()
                        Cmd.ExecuteNonQuery()
                        cnClose()

                        Afficher()
                        vider()
                        modif = 0
                        MsgBox("Client Modifier ", MsgBoxStyle.Information, "Réussi !")
                    Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                        cnClose()
                    End Try


                End If
            Else
                MsgBox("Entrez l'email Correct", MsgBoxStyle.Critical, "Erreur !")
            End If
        Else
            MsgBox("Vous n'avez selectionner aucun Client !", MsgBoxStyle.Critical, "Réssayer !")
        End If
    End Sub

    Private Sub Radio_trait_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Radio_trait.CheckedChanged
        If Radio_trait.Checked = True Then

            txt_nom_interl.Enabled = False

            txt_prenom.Enabled = True
            txt_nom.Enabled = True
            txt_cin.Enabled = True
            txt_nom_entrep.Enabled = True

            txt_nom_interl.Clear()

            txt_nom.Select()
            str = "Traiteur"
        End If
    End Sub

    Private Sub txt_portabl_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_portabl.KeyPress
        
    End Sub

    Private Sub txt_portabl_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_portabl.TextChanged
        Dim selStart As Integer = txt_portabl.SelectionStart
        Dim selMoveLeft As Integer = 0
        Dim newStr As String = "" 'Build a new string by copying each valid character from the existing string. The new string starts as blank and valid characters are added 1 at a time.

        For i As Integer = 0 To txt_portabl.Text.Length - 1

            If "0123456789".IndexOf(txt_portabl.Text(i)) <> -1 Then 'Characters that are in the allowed set will be added to the new string.
                newStr = newStr & txt_portabl.Text(i)

            ElseIf i < selStart Then 'Characters that are not valid are removed - if these characters are before the cursor, we need to move the cursor left to account for their removal.
                selMoveLeft = selMoveLeft + 1

            End If
        Next

        txt_portabl.Text = newStr 'Place the new text into the textbox.
        txt_portabl.SelectionStart = selStart - selMoveLeft 'Move the cursor to the appropriate location.
    End Sub

    Private Sub txt_fix_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_fix.TextChanged
        Dim selStart As Integer = txt_fix.SelectionStart
        Dim selMoveLeft As Integer = 0
        Dim newStr As String = "" 'Build a new string by copying each valid character from the existing string. The new string starts as blank and valid characters are added 1 at a time.

        For i As Integer = 0 To txt_fix.Text.Length - 1

            If "0123456789".IndexOf(txt_fix.Text(i)) <> -1 Then 'Characters that are in the allowed set will be added to the new string.
                newStr = newStr & txt_fix.Text(i)

            ElseIf i < selStart Then 'Characters that are not valid are removed - if these characters are before the cursor, we need to move the cursor left to account for their removal.
                selMoveLeft = selMoveLeft + 1

            End If
        Next

        txt_fix.Text = newStr 'Place the new text into the textbox.
        txt_fix.SelectionStart = selStart - selMoveLeft 'Move the cursor to the appropriate location.
    End Sub

    Private Sub txt_fax_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_fax.TextChanged
        Dim selStart As Integer = txt_fax.SelectionStart
        Dim selMoveLeft As Integer = 0
        Dim newStr As String = "" 'Build a new string by copying each valid character from the existing string. The new string starts as blank and valid characters are added 1 at a time.

        For i As Integer = 0 To txt_fax.Text.Length - 1

            If "0123456789".IndexOf(txt_fax.Text(i)) <> -1 Then 'Characters that are in the allowed set will be added to the new string.
                newStr = newStr & txt_fax.Text(i)

            ElseIf i < selStart Then 'Characters that are not valid are removed - if these characters are before the cursor, we need to move the cursor left to account for their removal.
                selMoveLeft = selMoveLeft + 1

            End If
        Next

        txt_fax.Text = newStr 'Place the new text into the textbox.
        txt_fax.SelectionStart = selStart - selMoveLeft 'Move the cursor to the appropriate location.
    End Sub

    Private Sub comb_ville_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles comb_ville.KeyPress
        e.Handled = True
    End Sub

    Private Sub txt_cin_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_cin.KeyPress
        If e.KeyChar = ChrW(32) Then
            e.Handled = True
        End If
    End Sub

    Private Sub txt_prenom_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_prenom.KeyPress
        If txt_prenom.Text.Length < 1 Then
            If Char.IsLower(e.KeyChar) Then
                'Convert to uppercase, and put at the caret position in the TextBox.
                txt_prenom.SelectedText = Char.ToUpper(e.KeyChar)
                e.Handled = True
            End If
        End If
    End Sub

    Private Sub txt_nom_interl_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_nom_interl.KeyPress
        If txt_nom_interl.Text.Length < 1 Then
            If Char.IsLower(e.KeyChar) Then
                'Convert to uppercase, and put at the caret position in the TextBox.
                txt_nom_interl.SelectedText = Char.ToUpper(e.KeyChar)
                e.Handled = True
            End If
        End If
    End Sub
End Class